let config = {
	deBug: true,
	isConnect: false
}

class Utils {
	static modelSql(name, options) {
		let sql;
		let sqlArr = [];
		let primaryKeyArr = [];
		let compositeUniqueKeyArr = [];

		for (const key in options) {
			if (Object.hasOwnProperty.call(options, key)) {
				const option = options[key];
				sqlArr.push(Utils.restrain(key, option));
				if (option.primaryKey === true) {
					primaryKeyArr.push(key.toString());
				}
				if (option.compositeUnique === true) {
					compositeUniqueKeyArr.push(key.toString());
				}
			}
		}


		let compositeUniqueConstraint = '';
		if (compositeUniqueKeyArr.length > 0) {
			compositeUniqueConstraint = `, UNIQUE (${compositeUniqueKeyArr.join(', ')})`;
		}

		if (primaryKeyArr.length > 1) {
			sql =
				`CREATE TABLE '${name}' (${sqlArr.join(', ').replaceAll(' PRIMARY KEY', '')}, PRIMARY KEY (${primaryKeyArr.join()})${compositeUniqueConstraint})`;
		} else {
			sql = `CREATE TABLE '${name}' (${sqlArr.join(', ')}${compositeUniqueConstraint})`;
		}
		return sql;
	}


	static indexSql(name, options) {
		return new Promise((resolve, reject) => {
			let indexKeyArr = [];
			for (const key in options) {
				if (Object.hasOwnProperty.call(options, key)) {
					const option = options[key];
					if (option.index == true) {
						indexKeyArr.push(key.toString());
					}
				}
			}

			if (indexKeyArr.length) {
				const promises = indexKeyArr.map(item => {
					return new Promise((resolveItem, rejectItem) => {
						plus.sqlite.executeSql({
							name: config.name,
							sql: `CREATE INDEX "${name}_${item}"ON "${name}" ("${item}")`,
							success(e) {
								resolveItem(e);
							},
							fail(e) {
								rejectItem(e);
							}
						});
					});
				});

				Promise.all(promises).then(resolve).catch(reject);
			} else {
				resolve();
			}
		});
	}

	static restrain(key, options) {
		let restrainArray = [];
		restrainArray.push(`'${key}'`);
		// 如果是 String 拦截处理
		if (options.constructor != Object) {
			restrainArray.push(Utils.toType(options));
			return restrainArray.join(' ');
		}
		restrainArray.push(Utils.toType(options.type));
		// 主键
		if (options.primaryKey == true) {
			if (options.autoIncrement != true) {
				restrainArray.push('PRIMARY KEY');
			}
		}
		// 自增
		if (Utils.isNumber(options.type) && options.autoIncrement == true) {
			restrainArray.pop();
			restrainArray.push('INTEGER');
			restrainArray.push('PRIMARY KEY');
			restrainArray.push('AUTOINCREMENT');
		}
		// 非空
		if (options.notNull == true) {
			restrainArray.push('NOT NULL');
		}
		// 默认值
		if (options.default != undefined) {
			restrainArray.push(`DEFAULT ${options.default}`);
		}
		// 是否是不同的值
		if (options.unique == true) {
			restrainArray.push('UNIQUE');
		}
		// 检查
		if (options.check) {
			restrainArray.push(`CHECK(${THIS_VALUE.check})`);
		}
		return restrainArray.join(' ');
	}

	static toType(jsType) {
		let sqliteType = '';
		if (Utils.isNumber(jsType)) {
			sqliteType = 'numeric';
		} else if (Utils.isDate(jsType)) {
			sqliteType = 'timestamp';
		} else {
			sqliteType = 'varchar';
		}
		return sqliteType;
	}

	static log() {
		if (config.deBug) {
			console.log.apply(null, arguments);
		}
	}

	static warn() {
		if (config.deBug) {
			console.warn.apply(null, arguments);
		}
	}

	static error() {
		console.error.apply(null, arguments);
	}

	static isArray(value) {
		return Object.prototype.toString.call(value) === '[object Array]'
	}
	static isObject(value) {
		return Object.prototype.toString.call(value) === '[object Object]'
	}
	static isString(value) {
		return Object.prototype.toString.call(value) === '[object String]'
	}
	static isFunction(value) {
		return (value === Function || Object.prototype.toString.call(value) === '[object Function]')
	}
	static isNumber(value) {
		return (value === Number || Object.prototype.toString.call(value) === '[object Number]')
	}
	static isNaN(value) {
		return (Object.prototype.toString.call(value) === '[object Number]' && isNaN(value))
	}
	static isBoolean(value) {
		return Object.prototype.toString.call(value) === '[object Boolean]'
	}
	static isUndefined(value) {
		return Object.prototype.toString.call(value) === '[object Undefined]'
	}
	static isModel(value) {
		return Object.prototype.toString.call(value) === '[object Model]'
	}
	static isDate(value) {
		return (value === Date || Object.prototype.toString.call(value) === '[object Date]')
	}
}

/**
 * Model 对象内部public方法全部 return this;
 */
class Model {

	_selectSql = 'SELECT %FIELD% FROM %TABLE%%JOIN%%WHERE%%ORDER%%LIMIT%'
	_insertSql = 'INSERT INTO %TABLE% (%FIELD%) VALUES (%DATA%)'
	_replaceSql = 'REPLACE INTO %TABLE% (%FIELD%) VALUES (%DATA%)'
	_updateSql = 'UPDATE %TABLE% SET %SET%%WHERE%%ORDER%%LIMIT%'
	_deleteSql = 'DELETE FROM %TABLE%%WHERE%%ORDER%%LIMIT%'
	_countsSql = 'SELECT COUNT(*) as Count FROM %TABLE%%WHERE%'

	_sqlWhere = []

	/**
	 * @constructor
	 * @param {String} table 数据库表名
	 * @param {} options 数据表列对象
	 * @returns 
	 */
	constructor(table, options) {
		let self = this;
		self.table = table;
		self.options = options;

		if (!config.isConnect) {
			usqlite.connect(config);
		}
	}
	async init() {
		if (config.isConnect) {
			try {
				await this.createTable();
			} catch (error) {
				console.error("模型初始化失败:", error);
			}
		} else {
			throw new Error("Database connection not established.");
		}
	}

	exists() {
		return new Promise((resolve, reject) => {
			let SQL = this.parseSelectSql()
			SQL = `SELECT EXISTS (${SQL}) AS 'isExist'`
			plus.sqlite.selectSql({
				name: config.name,
				sql: SQL,
				success: (res) => {
					if (res[0].isExist) {
						resolve(true)
					} else {
						reject(false)
					}
				},
				fail: (err) => {
					reject(false)
				}
			})
		})
	}

	first() {
		return new Promise((resolve, reject) => {
			this.limit(1)
			const SQL = this.parseSelectSql()
			console.log(SQL);
			plus.sqlite.selectSql({
				name: config.name,
				sql: SQL,
				success: (res) => {
					resolve(res[0])
				},
				fail: (err) => {
					reject(err)
				}
			})
		})
	}

	get() {
		return new Promise((resolve, reject) => {
			const SQL = this.parseSelectSql()
			plus.sqlite.selectSql({
				name: config.name,
				sql: SQL,
				success: (res) => {
					resolve(res)
				},
				fail: (err) => {
					reject(err)
				}
			})
		})
	}

	/**
	 * 改进后的join方法，支持更多类型的JOIN
	 * @param {String} table 关联的表名
	 * @param {String} onField 本表关联字段
	 * @param {String} relatedField 相关联表的字段
	 * @param {String} joinType JOIN类型，默认为INNER JOIN，可选LEFT JOIN等
	 * @returns {Model} 返回当前Model实例，支持链式调用
	 */
	join(table, onField, relatedField, joinType = 'INNER JOIN') {
		if (!table || !onField || !relatedField) {
			throw new Error('Invalid parameters for join method.');
		}
		// 动态构建JOIN子句，支持不同类型的JOIN
		this._join = `${joinType} ${table} ON ${this.table}.${onField} = ${table}.${relatedField}`;
		return this;
	}

	_parseWhere() {
		if (this._sqlWhere.length > 0) {
			let _w = [];
			this._sqlWhere.forEach((item, index) => {
				let condition = `${item.field} ${item.opt} ${item.val}`;
				if (_w.length > 0) {
					condition = `${item.logic} ${condition}`;
				}
				_w.push(condition);
			});
			this._sqlWhere = [];
			return ' WHERE ' + _w.join(' ');
		} else {
			return '';
		}
	}

	_parseFields() {
		let _f = '*'
		if (this._fields) {
			_f = this._fields
			this._fields = ''
		}
		return _f
	}

	_parseOrder() {
		let _o = ''
		if (this._order) {
			_o = ' ORDER BY ' + this._order
			this._order = ''
		}
		return _o
	}

	_parseData() {
		let _d = ''
		if (this._data) {
			_d = this._data
			this._data = ''
		}
		return _d
	}
	parseJoinSql() {
		if (this._join) {
			return ` ${this._join}`
		} else {
			return ''
		}
	}

	_parseLimit() {
		let _l = ''
		if (this._limit) {
			_l = ' LIMIT ' + this._limit
			this._limit = ''
		}
		if (this._offset) {
			_l += ' OFFSET ' + this._offset;
			this._offset = ''
		}
		return _l
	}

	_parseSet() {
		let _s = ''
		if (this._set) {
			_s = '' + this._set
			this._set = ''
		}
		return _s
	}

	_parseData() {
		let _d = ''
		if (this._data) {
			let keys = [];
			let values = [];
			for (var key in this._data) {
				if (this._data[key] !== null) {
					keys.push(key)
					values.push(`'${this._data[key]}'`)
				}
			}
			_d = values.join()
			this._fields = keys.join()
			this._data = {}
		}
		return _d
	}

	parseInsertSql() {
		let _sqlTpl = this._insertSql
		_sqlTpl = _sqlTpl.replace('%TABLE%', this.table)
		_sqlTpl = _sqlTpl.replace('%DATA%', this._parseData())
		_sqlTpl = _sqlTpl.replace('%FIELD%', this._parseFields())
		return _sqlTpl
	}

	parseReplaceSql() {
		let _sqlTpl = this._replaceSql
		_sqlTpl = _sqlTpl.replace('%TABLE%', this.table)
		_sqlTpl = _sqlTpl.replace('%DATA%', this._parseData())
		_sqlTpl = _sqlTpl.replace('%FIELD%', this._parseFields())
		return _sqlTpl
	}

	parseSelectSql() {
		let _sqlTpl = this._selectSql
		_sqlTpl = _sqlTpl.replace('%TABLE%', this.table)
		_sqlTpl = _sqlTpl.replace('%FIELD%', this._parseFields())
		_sqlTpl = _sqlTpl.replace('%WHERE%', this._parseWhere())
		_sqlTpl = _sqlTpl.replace('%ORDER%', this._parseOrder())
		_sqlTpl = _sqlTpl.replace('%LIMIT%', this._parseLimit())
		_sqlTpl = _sqlTpl.replace('%JOIN%', this.parseJoinSql()) // 添加JOIN部分
		return _sqlTpl
	}

	parseUpdateSql() {
		let _sqlTpl = this._updateSql
		_sqlTpl = _sqlTpl.replace('%TABLE%', this.table)
		_sqlTpl = _sqlTpl.replace('%WHERE%', this._parseWhere())
		_sqlTpl = _sqlTpl.replace('%SET%', this._parseSet())
		_sqlTpl = _sqlTpl.replace('%ORDER%', this._parseOrder())
		_sqlTpl = _sqlTpl.replace('%LIMIT%', this._parseLimit())
		return _sqlTpl
	}

	parseDeleteSql() {
		let _sqlTpl = this._deleteSql
		_sqlTpl = _sqlTpl.replace('%TABLE%', this.table)
		_sqlTpl = _sqlTpl.replace('%WHERE%', this._parseWhere())
		_sqlTpl = _sqlTpl.replace('%ORDER%', this._parseOrder())
		_sqlTpl = _sqlTpl.replace('%LIMIT%', this._parseLimit())
		return _sqlTpl
	}

	parseCountSql() {
		let _sqlTpl = this._countsSql
		_sqlTpl = _sqlTpl.replace('%TABLE%', this.table)
		_sqlTpl = _sqlTpl.replace('%WHERE%', this._parseWhere())
		return _sqlTpl
	}

	fields(fields) {
		this._fields = fields
		return this
	}

	// // 单一条件 and 优先
	// model.where({ field: 'age', val: 30 });
	// // 多条件 AND
	// model.where([
	//     { field: 'age', opt: '>', val: 30, logic: 'AND' },
	//     { field: 'age', opt: '<', val: 50, logic: 'AND' }
	// ]);
	// // 多条件 OR
	// model.where([
	//     { field: 'age', opt: '>', val: 30, logic: 'OR' },
	//     { field: 'name', val: 'John', logic: 'OR' }
	// ]);
	// // 复杂条件混合
	//WHERE (age > '30') OR (name = 'John' AND status = 'active')
	// model.where([
	//     { field: 'age', opt: '>', val: 30, logic: 'AND' },
	//     { field: 'name', val: 'John', logic: 'OR' },
	//     { field: 'status', val: 'active', logic: 'AND' }
	// ]);

	where(condition) {
		if (Utils.isObject(condition)) {
			this._sqlWhere.push({
				field: condition.field,
				opt: condition.opt || '=',
				val: condition.val,
				logic: condition.logic || 'AND'
			});
		} else if (Utils.isArray(condition)) {
			condition.forEach(cond => {
				if (Utils.isObject(cond)) {
					this._sqlWhere.push({
						field: cond.field,
						opt: cond.opt || '=',
						val: cond.val,
						logic: cond.logic || 'AND'
					});
				}
			});
		} else {
			throw new Error('Invalid condition format');
		}
		return this;
	}



	limit(limit, offset) {
		this._limit = limit
		this._offset = offset
		return this
	}

	order(order) {
		this._order = order
		return this
	}

	set(obj) {
		let items = []
		for (var key in obj) {
			items.push(`${key}='${obj[key]}'`);
		}
		this._set = items.join()
	}

	data(data) {
		this._data = data
	}

	update(obj) {
		this.set(obj)
		const sql = this.parseUpdateSql()
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: config.name,
				sql: sql,
				success(e) {
					resolve(e)
				},
				fail(e) {
					reject(e)
				}
			})
		})
	}

	delete() {
		const sql = this.parseDeleteSql()
		console.log(sql);
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: config.name,
				sql: sql,
				success(e) {
					resolve(e)
				},
				fail(e) {
					reject(e)
				}
			})
		})
	}

	count() {
		const sql = this.parseCountSql()
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: sql,
				success(e) {
					resolve(e[0].Count)
				},
				fail(e) {
					reject(e)
				}
			})
		})
	}

	replace(options) {
		this.data(options)
		const sql = this.parseReplaceSql()
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: config.name,
				sql: sql,
				success: (e) => {
					resolve(e)
				},
				fail: (err) => {
					reject(err)
				}
			})
		})
	}


	create(options) {
		this.data(options)
		const sql = this.parseInsertSql()
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: config.name,
				sql: sql,
				success: (e) => {
					resolve(e)
					// this.selectSql(`SELECT last_insert_rowid() FROM '${this.table}' LIMIT 1`).then(res => {
					// 	resolve(res[0]['last_insert_rowid()'])
					// }).catch(err => {
					// 	resolve(-1)
					// })
				},
				fail: (err) => {
					reject(err)
				}
			})
		})
	}


	/**
	 * 未测试  ai生成的
	 * 批量插入数据
	 * @param {Array<Object>} records 待插入的记录数组
	 * @return {Promise} 执行结果的Promise
	 */
	batchInsert(records) {
	    if (!Array.isArray(records) || records.length === 0) {
	        return Promise.reject(new Error("Records must be a non-empty array."));
	    }
	
	    const keys = Object.keys(records[0]);
	    const placeholders = records.map(record => {
	        return '(' + keys.map(key => {
	            const value = record[key];
	            if (typeof value === 'string') {
	                return `'${value.replace(/'/g, "''")}'`; // 处理字符串中的单引号
	            }
	            return value;
	        }).join(', ') + ')';
	    }).join(', ');
	
	    const sql = `INSERT INTO ${this.table} (${keys.join(', ')}) VALUES ${placeholders}`;
	    // console.log(sql);
	    return new Promise((resolve, reject) => {
	        plus.sqlite.executeSql({
	            name: config.name,
	            sql: sql,
	            success(e) {
	                resolve(e);
	            },
	            fail(e) {
	                reject(e);
	            }
	        });
	    });
	}
	
	
	/**
	 * 未测试  ai生成的
	 * 批量插入数据
	 * @param {Array<Object>} records 待插入的记录数组
	 * @return {Promise} 执行结果的Promise
	 */
	batchReplace(records) {
	    if (!Array.isArray(records) || records.length === 0) {
	        return Promise.reject(new Error("Records must be a non-empty array."));
	    }
	
	    const keys = Object.keys(records[0]);
	    const placeholders = records.map(record => {
	        return '(' + keys.map(key => {
	            const value = record[key];
	            if (typeof value === 'string') {
	                return `'${value.replace(/'/g, "''")}'`; // 处理字符串中的单引号
	            }
	            return value;
	        }).join(', ') + ')';
	    }).join(', ');
	
	    const sql = `REPLACE INTO ${this.table} (${keys.join(', ')}) VALUES ${placeholders}`;
	    return new Promise((resolve, reject) => {
	        plus.sqlite.executeSql({
	            name: config.name,
	            sql: sql,
	            success(e) {
	                resolve(e);
	            },
	            fail(e) {
	                reject(e);
	            }
	        });
	    });
	}
	
	
	/**
	 * 未测试  ai生成的
	 * 批量插入数据或修改
	 * @param {Array<Object>} records 待插入的记录数组
	 * @return {Promise} 执行结果的Promise
	 */
	batchAddOrEdit(records, conflictKey) {
	    if (!Array.isArray(records) || records.length === 0) {
	        return Promise.reject(new Error("Records must be a non-empty array."));
	    }
	
	    if (!conflictKey || typeof conflictKey !== 'string') {
	        return Promise.reject(new Error("A valid conflict key must be provided."));
	    }
	
	    const keys = Object.keys(records[0]);
	    const placeholders = records.map(record => {
	        return '(' + keys.map(key => {
	            const value = record[key];
	            if (typeof value === 'string') {
	                return `'${value.replace(/'/g, "''")}'`; // 处理字符串中的单引号
	            }
	            return value;
	        }).join(', ') + ')';
	    }).join(', ');
	
	    // 构建 UPDATE 部分的 SQL 语句
	    const updateAssignments = keys.map(key => {
	        return `${key} = excluded.${key}`;
	    }).join(', ');
	
	    // 使用 INSERT INTO ... ON CONFLICT ... DO UPDATE 语法
	    const sql = `
	        INSERT INTO ${this.table} (${keys.join(', ')}) 
	        VALUES ${placeholders}
	        ON CONFLICT(${conflictKey}) DO UPDATE SET ${updateAssignments};
	    `;
	    return new Promise((resolve, reject) => {
	        plus.sqlite.executeSql({
	            name: config.name,
	            sql: sql,
	            success(e) {
	                resolve(e);
	            },
	            fail(e) {
	                reject(e);
	            }
	        });
	    });
	}

	executeSql(sql) {
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: config.name,
				sql: sql,
				success: (e) => {
					resolve(e);
				},
				fail: (e) => {
					reject(e);
				}
			})
		})
	}

	selectSql(sql) {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: sql,
				success: (e) => {
					resolve(e);
				},
				fail: (e) => {
					reject(e);
				}
			})
		})
	}
	//计算某个字段的总和
	sum(name) {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: `SELECT sum(${name}) FROM ${this.table}`,
				success: (e) => {
					resolve(e[0]['sum(unread_count)']);
				},
				fail: (e) => {
					reject(e);
				}
			})
		})
	}


	/**
	 * @description 判断是否存在 
	 * @param {Function} callback 
	 */
	isExist(callback) {
		if (callback && !Utils.isFunction(callback)) {
			Utils.error('The type of "callback" is wrong, it should be "Function".');
		}
		let sql = `SELECT count(*) AS isExist FROM sqlite_master WHERE type='table' AND name='${this.table}'`;
		let self = this;
		plus.sqlite.selectSql({
			name: config.name,
			sql: sql,
			success(e) {
				callback(null, e);
			},
			fail(e) {
				callback(e);
			}
		});
		return this;
	}

	drop() {
		this.repair();
		var sql = `DROP TABLE '${this.table}'`;
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: sql,
				success(e) {
					resolve(e)
				},
				fail(e) {
					reject(e)
				}
			})
		})
	}
	
	createTable() {
	    return new Promise((resolve, reject) => {
	        this.isExist((err, result) => {
	            if (err) {
	                reject(err);
	            } else if (!result[0].isExist) {
	                let sql = Utils.modelSql(this.table, this.options);
	                plus.sqlite.executeSql({
	                    name: config.name,
	                    sql: sql,
	                    success: (e) => {
	                        Utils.indexSql(this.table, this.options)
	                            .then(() => resolve(e))
	                            .catch(reject);
	                    },
	                    fail: reject
	                });
	            } else {
	                resolve(); // 表已存在，也算作成功
	            }
	        });
	    });
	}

	toString() {
		return `[${this.table} Model]`;
	}
}

// 单例模式
export default class usqlite {
	/**
	 * @description 链接数据库
	 * @param {Object} options 数据库配置信息 *
	 * {name: 'demo', path: '_doc/demo.db'}
	 * - name 数据库名称*
	 * - path 数据库路径
	 * @param {Function} callback 
	 */
	static connect(options, callback) {
		config.name = options.name; // 数据库名称*
		config.path = options.path; // 数据库名称*
		plus.sqlite.openDatabase({
			name: config.name, //数据库名称
			path: config.path, //数据库地址
			success(e) {
				config.isConnect = true;
				callback(null, e);
			},
			fail(e) {
				if (e.code == -1402) {
					config.isConnect = true;
				}
				callback(e);
			}
		})
	}

	/**
	 * @description 断开数据库
	 * @param {*} callback 
	 */
	static close(callback) {
		plus.sqlite.closeDatabase({
			name: config.name, //数据库名称
			path: config.path, //数据库地址
			success(e) {
				config.isConnect = false;
				callback(null, e);
			},
			fail(e) {
				callback(e);
			}
		});
	}
	/**
	 * @description 创建 Model 对象
	 * @example
	 * @param {String} name 数据表名称 *
	 * @param {String} options 参数配置 *
	 * @returns 返回 Model 对象
	 */
	static model(name, options) {
		return new Model(name, options);
	}
}